using System;
using System.Data;
using System.Data.SqlClient;

namespace QUAN_LY_NGAN_HANG_CAU_HOI_TRAC_NGHIEM
{
    public class CauHoiDAL
    {

        private Guid _maSoCauHoi;
        public Guid MaSoCauHoi
        {
            get { return _maSoCauHoi; }
            set { _maSoCauHoi = value; }
        }

        private String _noiDung;
        public String NoiDung
        {
            get { return _noiDung; }
            set { _noiDung = value; }
        }

        private Guid _mucDo;
        public Guid MucDo
        {
            get { return _mucDo; }
            set { _mucDo = value; }
        }

        private Guid _linhVuc;
        public Guid LinhVuc
        {
            get { return _linhVuc; }
            set { _linhVuc = value; }
        }

        private Guid _giaoVien;
        public Guid GiaoVien
        {
            get { return _giaoVien; }
            set { _giaoVien = value; }
        }

        private float _tanSuatDung;
        public float TanSuatDung
        {
            get { return _tanSuatDung; }
            set { _tanSuatDung = value; }
        }

       public static Guid InsertCauHoi(CauHoiDAL cauhoi)
        {
            using (SqlConnection cn = new SqlConnection(Globals.ConnectionString))
            {
                Guid id = Guid.Empty;
                SqlCommand cmd = new SqlCommand("usp_InsertCauHoi", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MaCauHoi", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@NoiDung", SqlDbType.NVarChar, 250).Value = cauhoi.NoiDung;
                cmd.Parameters.Add("@MucDo", SqlDbType.UniqueIdentifier).Value = cauhoi.MucDo;
                cmd.Parameters.Add("@LinhVuc", SqlDbType.UniqueIdentifier).Value = cauhoi.LinhVuc;
                cmd.Parameters.Add("@GiaoVien", SqlDbType.UniqueIdentifier).Value = cauhoi.GiaoVien;
                cmd.Parameters.Add("@TanSuatDung", SqlDbType.Float).Value = cauhoi.TanSuatDung;
                cn.Open();
                cmd.ExecuteNonQuery();
                id = (Guid)cmd.Parameters["@MaCauHoi"].Value;
                return id;
            }
        }

       public static DataTable LayDanhSachCauHoiTheoDieuKien(Guid MaGV, Guid MaLV, Guid MaMD)
        {
            String sqlMaGV = " 1=1";
            String sqlMaLV = " 1=1";
            String sqlMaMD = " 1=1";

            if (MaGV != Guid.Empty)
                sqlMaGV = " CAUHOI.GiaoVien = '" + MaGV + "'";
            if (MaLV != Guid.Empty)
                sqlMaLV = " CAUHOI.LinhVuc = '" + MaLV + "'";
            if (MaMD != Guid.Empty)
                sqlMaMD = " CAUHOI.MucDO = '" + MaMD + "'";

            String sql = "";
            sql = "select CAUHOI.MaSoCauHoi, CAUHOI.NOIDUNG, LINHVUC.ChuThich, GIAOVIEN.TenGiaoVien, MUCDO.ChuThich " + 
                  "from CAUHOI, MUCDO, GIAOVIEN, LINHVUC where " +
                  sqlMaGV + " and" + sqlMaLV + " and" + sqlMaMD + " and " +
                  "CAUHOI.MucDo = MUCDO.MaSoMucDO" + " and " +
                  "CAUHOI.LinhVuc = LINHVUC.MaSoLinhVuc" + " and " +
                  "CAUHOI.GiaoVien = GIAOVIEN.MaSoGiaoVien";

            using (SqlConnection cn = new SqlConnection(Globals.ConnectionString))
            {
                DataTable Kq = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(sql, cn);
                    adapter.Fill(Kq);
                return Kq;
            }
        }
    }

}